package cn.zd.demo1.sql;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;
import android.widget.Toast;

import androidx.annotation.Nullable;

import java.util.ArrayList;
import java.util.List;

import cn.zd.demo1.SQLiteActivity;
import cn.zd.demo1.sql.entity.TabUserInfo;

public class UserDBHelper extends SQLiteOpenHelper {

    private static UserDBHelper helper = null;
    private static final String DB_NAME = "userdb";
    private static final String DB_TABLE_NAME = "tab_user_info";

    private SQLiteDatabase readDB;
    private SQLiteDatabase writeDB;
    private Context context;
    private static final String SQL_CREATE = "CREATE TABLE  IF NOT EXISTS "+DB_TABLE_NAME+" (" +
            "  user_id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL," +
            "  user_name VARCHAR NOT NULL," +
            "  user_age INTEGER NOT NULL" +
            ");";

    private UserDBHelper(@Nullable Context context,int version) {
        super(context, DB_NAME, null, version);
    }
    public static UserDBHelper getInstance(Context context,int version) {
        if(helper == null) {
            helper = new UserDBHelper(context, version);
        }
        return helper;
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL(SQL_CREATE);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        Log.i("SqlLite", String.format("版本升级：%d -> %d",oldVersion,newVersion));
    }
    // 打开写
    public SQLiteDatabase openWrite(){
        if(writeDB == null || !writeDB.isOpen()) {
            writeDB = helper.getWritableDatabase();
        }
        return writeDB;
    }
    // 打开读
    public SQLiteDatabase openRead(){
        if(readDB == null || !readDB.isOpen()) {
            readDB = helper.getReadableDatabase();
        }
        return readDB;
    }
    public void closeDb(){
        if(writeDB != null && writeDB.isOpen()) {
            writeDB.close();
        }
        if(readDB != null && readDB.isOpen()) {
            readDB.close();
        }
    }

    public long insert(TabUserInfo userInfo) {
        ContentValues values = new ContentValues();
        values.put("user_name",userInfo.getUserName());
        values.put("user_age",userInfo.getUserAge());
        // 执行插入记录，该语句返回插入记录行号
        // 1. 如果第三个参数values 为null或其中没有元素，insert() 方法就要求必须添加一条除了主键之外其他字段为null的记录 （主键自增）
        //  为了满足SQL语法的要求，insert语句必须给定一个字段名（第二个参数），如不给字段名sql变成如 insert into xxx () values() 这样的语句显然是错误的,
        // 如第二个参数为name ，SQL： insert into xxx (name) values(null)
        // 2. 如果第三个参数不为null且元素个数大于0，可以把第二个参数设置为null
        return writeDB.insert(DB_TABLE_NAME,null,values);
    }
    public void select(){

    }
    public long deleteByName(String name){
        return writeDB.delete(DB_TABLE_NAME,"user_name = ?",new String[]{name});
    }
    public long deleteAll(){
        return writeDB.delete(DB_TABLE_NAME, "1=1",null);
    }
    public long update(String newUserName,String oldUserName){
        ContentValues values = new ContentValues();
        values.put("user_name",newUserName);
        return writeDB.update(DB_TABLE_NAME,values,"user_name = ?",new String[]{oldUserName});
    }
    public long updateAll(String newUserName){
        ContentValues values = new ContentValues();
        values.put("user_name",newUserName);
        return writeDB.update(DB_TABLE_NAME,values,"1 = 1",null);
    }
    public List<TabUserInfo> queryAll(){
        List<TabUserInfo> userInfos = new ArrayList<>();
        Cursor query = readDB.query(DB_TABLE_NAME, null, null, null, null, null, null);
        while (query.moveToNext()) {
            TabUserInfo userInfo = new TabUserInfo();
            userInfo.setUserId(query.getInt(0));
            userInfo.setUserName(query.getString(1));
            userInfo.setUserAge(query.getInt(2));
            userInfos.add(userInfo);
        }
        return userInfos;
    }
    public List<TabUserInfo> query(String name){
        List<TabUserInfo> userInfos = new ArrayList<>();
        Cursor query = readDB.query(DB_TABLE_NAME, null, "user_name like ?", new String[]{name}, null, null, null);
        while (query.moveToNext()) {
            TabUserInfo userInfo = new TabUserInfo();
            userInfo.setUserId(query.getInt(0));
            userInfo.setUserName(query.getString(1));
            userInfo.setUserAge(query.getInt(2));
            userInfos.add(userInfo);
        }
        return userInfos;
    }
}
